﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

// EEJ Added usings
using System.Data;
using System.Data.SqlServerCe;
using ErikEJ.SqlCe;
using System.IO;
using System.Reflection;

namespace BuildSDFDatabase
{
	public class Database
	{
		public static void Create()
		{
		}
	}
	public class DBTables
	{
		//table ClusterInfoSummary
		//unique index Field + Timestamp + ClusterID
		public static string ClusterInfoTable =		@"Create Table ClusterInfo(
													Field		nvarchar(31) not null,
													ClusterID	int not null,
													Timestamp	datetime not null,
													Size		int not null,
													Constraint	pk_ClusterInfo Primary Key(Field, Timestamp, ClusterID))";
		//public static string ClusterInfoSummaryIndex = @"Create Unique Index ClusterInfoSummary_Index on ClusterInfoSummary(Field, ClusterID, Timestamp)";

		//table ClusterCorpusSummary
		//unique index Field + Timestamp + ClusterID + Corpus
		public static string ClusterCorpusTable = @"Create Table ClusterCorpus(
													Field		nvarchar(31) not null,
													ClusterID	int not null,
													Timestamp	datetime not null,
													Corpus,		nvarchar(31) not null,
													Size		int not null,
													Constraint	pk_ClusterCorpus Primary Key(Field, Timestamp, ClusterID, Corpus))";
//		public static string ClusterCorpusSummaryIndex = @"Create Unique Index ClusterCorpusSummary_Index on 
//															ClusterCorpusSummary(Field, ClusterID, Timestamp, Corpus)";

		//table ClusterCorpusKeywordSummary
		//unique index Field + Timestamp + ClusterID + Corpus + Keyword
		public static string ClusterCorpusKeyword = @"Create Table ClusterCorpusKeyword(
													Field		nvarchar(31) not null,
													ClusterID	int not null,
													Timestamp	datetime not null,
													Corpus,		nvarchar(31) not null,
													Keyword		nvarchar(31) not null,
													Frequency	int,
													Constraint	pk_ClusterCorpusKeyword Primary Key(Field, Timestamp, ClusterID, Corpus, Keyword))";
//		public static string ClusterCorpusKeywordIndex = @"Create Unique Index ClusterCorpusSummary_Index on 
//															ClusterCorpusSummary(Field, ClusterID, Timestamp, Corpus, Keyword)";

		//table Document
		public static string Document = @"Create Table Document(
													DocumentID		int primary key,
													Timestamp		datetime not null,
													Title,			nvarchar(1024),
													Abstract		nvarchar(max),
													Corpus			nvarchar(31) not null)";

		//table Document properties
		public static string DocumentProperties = @"Create Table DocumentProperties(
													PropertyID		int identity,
													DocumentID		int foreign key references Document(DocumentID),
													PropertyName	nvarchar(31),
													PropertyValue	ntext)";
		public static string DocumentPropertyID_Index = @"Create Index DocPropertyID_Index on DocumentProperties(DocumentID)";

		//table ClusterDocument
		public static string ClusterDocument = @"Create Table ClusterDocument(
													DocumentID		int foreign key references Document(DocumentID),
													Field			nvarchar(31) not null,
													ClusterID		int not null,
													constraint	pk_ClusterDoc primary key(DocumentID))";
		public static string ClusterDocumentID_Index = @"Create Index ClusterDocID_Index on DocumentProperties(DocumentID)";

		//table RoseTree
		//unique index Field + Child
		//with topic id to be a number, bundle startwith "b" with bundle number
		public static string RoseTree = @"Create Table RoseTree(
																Field		nvarchar(31) not null,
																Child		nvarchar(31) not null,
																Parent		nvarchar(31) not null,
																Constraint	pk_RoseTree Primary Key(Field, Child))";

		//table GlobalLeadLag
		//unique index: Field + ObjectID + Corpus1 + Corpus2
		//topic, bundle, global
		public static string GlobalLeadLag = @"Create Table GlobalLeadLag(
													Field			nvarchar(31) not null,
													ObjectID		nvarchar(31) not null,
													Corpus1			nvarchar(31) not null,
													Corpus2			nvarchar(31) not null,
													LeadLagValue	float,
													Constraint	pk_GlobalLeadLag Primary Key(Field, ObjectID, Corpus1, Corpus2))";

		//table LocalLeadLag
		//unique index: Field + ObjectID + Timestamp + Corpus1 + Corpus2
		//only topic
		public static string LocalLeadLag = @"Create Table LocalLeadLag(
													Field			nvarchar(31) not null,
													ClusterID		int not null,
													Timestamp		datetime not null,
													Corpus1			nvarchar(31) not null,
													Corpus2			nvarchar(31) not null,
													LeadLagValue	float,
													Constraint	pk_LocalLeadLag Primary Key(Field, ObjectID, Timestamp, Corpus1, Corpus2))";

		//table DocumentMatrix
		//unique index DocURI1 + DocURI2
		//index DocURI1, DocURI2
		public static string DocumentMatrix = @"Create Table DocumentMatrix(
													Field			nvarchar(31) not null,
													ClusterID		int not null,
													DocURI1			int not null,
													DocURI2			int not null,
													Similarity		float,
													Constraint	pk_DocumentMatrix Primary Key(DocURI1, DocURI2))";
	}
}
